package com.jie.dao.impl;

import com.jie.dao.AgencyDao;
import com.jie.entity.Agency;
import com.jie.entity.Region;
import com.jie.entity.User;
import com.jie.util.BaseDao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 查询代理信息   默认查所有  并分页
 * 因为使用的是级联  默认省份和市是有值的  所以不用判断
 *
 */
public class AgencyDaoImpl implements AgencyDao
{
    @Override
    public List<Agency> find(int page, int size)
    {
        List<Agency> list = new ArrayList<Agency>( );
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        String sql = "select * from agency WHERE 1=1";
        sql += "  limit ?,?";

        conn = BaseDao.getConnection();
        try {
            stat = conn.prepareStatement(sql);
            stat.setInt(1, page);
            stat.setInt(2, size);
            rs = stat.executeQuery();
            while (rs.next()){
                Agency agency = new Agency();
                agency.setAgencyid(rs.getInt("agencyid"));
                agency.setUid(rs.getInt("uid"));
                agency.setDrank(rs.getInt("drank"));
                agency.setAffiliating(rs.getInt("affiliating"));
                agency.setUlist(new UserDaoImpl().findUser(agency.getUid()));

                List<Region> rlist = new ArrayList<Region>();
                rlist.add(new RegionDaoImpl().find(agency.getUlist().get(0).getUprovince()));
                rlist.add(new RegionDaoImpl().find(agency.getUlist().get(0).getUcity()));
                rlist.add(new RegionDaoImpl().find(agency.getUlist().get(0).getUcounty()));
                rlist.add(new RegionDaoImpl().find(agency.getAffiliating()));
                agency.setRlist(rlist);
                list.add(agency);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.closeConnection(conn,stat,rs);
        }

        return list;
    }


    @Override
    public int find()
    {
        int count = 0;
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        String sql = "select count(1) from agency where 1 = 1 ";
        try {
            conn = BaseDao.getConnection();
            stat = conn.prepareStatement(sql);
            rs = stat.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.closeConnection(conn,stat,rs);
        }
        return count;
    }

    @Override
    public int delAgency(int agencyid)
    {
        String sql = "delete from agency where agencyid = ?";
        Object[] object = {
                agencyid
        };
        return BaseDao.executeUpdate(sql,object);
    }

    @Override
    public int findByuid(int uid)
    {
        int num = 0;
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        String sql = "select agencyid from agency where uid = ? ";
        try {
            conn = BaseDao.getConnection();
            stat = conn.prepareStatement(sql);
            stat.setInt(1,uid);
            rs = stat.executeQuery();
            if (rs.next()) {
                num = rs.getInt("agencyid");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.closeConnection(conn,stat,rs);
        }
        return num;
    }

    @Override
    public int update(Agency agency)
    {
        String sql = "UPDATE agency SET `uid`= ?, `drank`= ?, `affiliating`= ?  WHERE agencyid= ?";
        Object[] objects = {
                agency.getUid(),
                agency.getDrank(),
                agency.getAffiliating(),
                agency.getAgencyid()
        };
        return BaseDao.executeUpdate(sql,objects);


    }

    @Override
    public Agency findAgency(int agencyid)
    {
        Agency agency = null;
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        String sql = "select * from agency WHERE agencyid = ?";
//        sql += "  limit ?,?";

        conn = BaseDao.getConnection();
        try {
            stat = conn.prepareStatement(sql);
            stat.setInt(1, agencyid);
            rs = stat.executeQuery();
            while (rs.next()){
                agency = new Agency();
                agency.setAgencyid(rs.getInt("agencyid"));
                agency.setUid(rs.getInt("uid"));
                agency.setDrank(rs.getInt("drank"));
                agency.setAffiliating(rs.getInt("affiliating"));
                agency.setUlist(new UserDaoImpl().findUser(agency.getUid()));

                List<Region> rlist = new ArrayList<Region>();
                rlist.add(new RegionDaoImpl().find(agency.getUlist().get(0).getUprovince()));
                rlist.add(new RegionDaoImpl().find(agency.getUlist().get(0).getUcity()));
                rlist.add(new RegionDaoImpl().find(agency.getUlist().get(0).getUcounty()));
                rlist.add(new RegionDaoImpl().find(agency.getAffiliating()));
                agency.setRlist(rlist);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.closeConnection(conn,stat,rs);
        }
        return agency;
    }
}
